package com.css.apps.base.user.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.css.apps.base.sys.model.SSys;
import com.css.db.query.QueryCache;

public class UserCountGraphDao {

	@SuppressWarnings("unchecked")
	public List<Object> getRegistUserCount(int year, List<String> orgIds) {
		StringBuilder hql = new StringBuilder();
		hql.append(" select")
		.append(" case when a.issueDate >= '").append(year).append("-01-01' and a.issueDate < '").append(year).append("-02-01' then '1'")
		.append(" when a.issueDate >= '").append(year).append("-02-01' and a.issueDate < '").append(year).append("-03-01' then '2'")
		.append(" when a.issueDate >= '").append(year).append("-03-01' and a.issueDate < '").append(year).append("-04-01' then '3'")
		.append(" when a.issueDate >= '").append(year).append("-04-01' and a.issueDate < '").append(year).append("-05-01' then '4'")
		.append(" when a.issueDate >= '").append(year).append("-05-01' and a.issueDate < '").append(year).append("-06-01' then '5'")
		.append(" when a.issueDate >= '").append(year).append("-06-01' and a.issueDate < '").append(year).append("-07-01' then '6'")
		.append(" when a.issueDate >= '").append(year).append("-07-01' and a.issueDate < '").append(year).append("-08-01' then '7'")
		.append(" when a.issueDate >= '").append(year).append("-08-01' and a.issueDate < '").append(year).append("-09-01' then '8'")
		.append(" when a.issueDate >= '").append(year).append("-09-01' and a.issueDate < '").append(year).append("-10-01' then '9'")
		.append(" when a.issueDate >= '").append(year).append("-10-01' and a.issueDate < '").append(year).append("-11-01' then '10'")
		.append(" when a.issueDate >= '").append(year).append("-11-01' and a.issueDate < '").append(year).append("-12-01' then '11'")
		.append(" when a.issueDate >= '").append(year).append("-12-01' and a.issueDate < '").append(year+1).append("-01-01' then '12'")
		.append(" else 'c' end,")
		.append(" count(a.uuid)")
		.append(" from SUser a where a.uuid in (select b.userId from SUserOrg b where b.orgId in (:orgIds))")
		.append(" and a.issueDate >= '").append(year).append("-01-01' and a.issueDate < '")
		.append(year + 1).append("-01-01'")
		.append(" group by")
		.append(" case when a.issueDate >= '").append(year).append("-01-01' and a.issueDate < '").append(year).append("-02-01' then '1'")
		.append(" when a.issueDate >= '").append(year).append("-02-01' and a.issueDate < '").append(year).append("-03-01' then '2'")
		.append(" when a.issueDate >= '").append(year).append("-03-01' and a.issueDate < '").append(year).append("-04-01' then '3'")
		.append(" when a.issueDate >= '").append(year).append("-04-01' and a.issueDate < '").append(year).append("-05-01' then '4'")
		.append(" when a.issueDate >= '").append(year).append("-05-01' and a.issueDate < '").append(year).append("-06-01' then '5'")
		.append(" when a.issueDate >= '").append(year).append("-06-01' and a.issueDate < '").append(year).append("-07-01' then '6'")
		.append(" when a.issueDate >= '").append(year).append("-07-01' and a.issueDate < '").append(year).append("-08-01' then '7'")
		.append(" when a.issueDate >= '").append(year).append("-08-01' and a.issueDate < '").append(year).append("-09-01' then '8'")
		.append(" when a.issueDate >= '").append(year).append("-09-01' and a.issueDate < '").append(year).append("-10-01' then '9'")
		.append(" when a.issueDate >= '").append(year).append("-10-01' and a.issueDate < '").append(year).append("-11-01' then '10'")
		.append(" when a.issueDate >= '").append(year).append("-11-01' and a.issueDate < '").append(year).append("-12-01' then '11'")
		.append(" when a.issueDate >= '").append(year).append("-12-01' and a.issueDate < '").append(year+1).append("-01-01' then '12'")
		.append(" else 'c' end");
		return new QueryCache(hql.toString()).setParameter("orgIds", orgIds).list();
	}
	
	public String getAllUserSum() {
		StringBuffer sb = new StringBuffer("select count(uuid) from SUser where delFlag = '2' and openFlag = '1' and userType = '1' ");
		QueryCache qc = new QueryCache(sb.toString());
		return String.valueOf(qc.uniqueResult());
	}
	
	@SuppressWarnings("unchecked")
	public List<Object[]> getUserCountBySys() {
		StringBuilder sb = new StringBuilder();
		String validUserSQL = " select u.uuid from SUser u where u.openFlag = '1' and u.delFlag = '2' and u.activeStatus = '3'";
		String validRoleSQL = " select r.uuid from SRole r where r.roleType = '1' and r.delFlag = '2' and r.openFlag = '1'";
//		String validSysSQL = " select s.uuid from SSys s";
		sb.append("select ur.sysId, count(distinct ur.userId) from SUserRole ur where ur.userId in (")
			.append(validUserSQL).append(") and ur.roleId in (").append(validRoleSQL).append(")")
			.append(" group by ur.sysId");
		List<Object[]> res = new QueryCache(sb.toString()).list();
		return res;
	}
	
	
	
	// -------------------------------------------------SYS----------------------------------------------------------

	@SuppressWarnings("unchecked")
	public List<SSys> getAllSysList() {
		return new QueryCache("from SSys").list(); 
	}
	
	public Map<String, String> getAllSysNameIdMap() {
		List<SSys> sysList = getAllSysList();
		Map<String, String> sysInfo = new HashMap<>();
		if(sysList != null) {
			for (SSys sys : sysList) {
				sysInfo.put(sys.getSysId(), sys.getName());
			}
		}
		return sysInfo;
	}
	
}
